package net.pws.oos.biz.query.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import net.pws.common.persistence.IPage;
import net.pws.common.persistence.QueryRepository;
import net.pws.common.util.DateUtils;
import net.pws.oos.biz.model.Organization;
import net.pws.oos.biz.model.ProspectRecod;
import net.pws.oos.biz.model.Task;
import net.pws.oos.biz.query.SystemManageFinder;
import net.pws.oos.biz.query.TaskFinder;
import net.pws.oos.biz.query.parameter.DataScope;
import net.pws.oos.biz.query.parameter.TaskQueryParameter;
import net.pws.oos.biz.query.parameter.TaskStatsByProjectQueryParameter;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Hibernate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@SuppressWarnings({ "rawtypes", "unchecked" })
public class DefaultTaskFinder extends AbstractFinder implements TaskFinder {
    
    @Autowired
    private SystemManageFinder systemManageFinder;
    
    public int countSubTask(String id) {
        Number count = (Number) entityManager.findFirst("select count(t.id) from Task t where t.parent.id = ? ",
                                                        new Object[] { id });
        return count.intValue();
    }
    
    public List<Task> getSubTaskList(String id) {
        return (List<Task>) entityManager.findList("from Task t left join t.project where t.parent.id = ? ",
                                                   new Object[] { id });
    }
    
    public List<Map> getSubTaskListMap(String taskId) {
        StringBuffer sql = new StringBuffer(QueryRepository.getInstance()
                                                           .get("sql.getSubTaskList")).append(" where t.parent_id = ? ");
        return (List<Map>) entityManager.nativeFindList(sql.toString(),
                                                        new Object[] { taskId,
                                                                      taskId });
    }
    
    public List<Task> getSubTaskList_(String id) {
        return (List<Task>) entityManager.findList("from Task t left join fetch t.district left join fetch t.project left join fetch t.executeOrganization left join fetch t.executePerson left join fetch t.creator left join fetch t.parent left join fetch t.result where t.parent.id = ? ",
                                                   new Object[] { id });
    }
    
    public int countTask(String executePersonId, String status) {
        TaskQueryParameter queryParameter = new TaskQueryParameter();
        queryParameter.setExecPersonId(executePersonId);
        queryParameter.setStatus(status);
        return countTask(queryParameter);
    }
    
    public int countTask(TaskQueryParameter queryParameter) {
        StringBuilder hql = new StringBuilder("select count(t.id) from Task t where t.parent is null ");
        List<Object> params = new ArrayList<Object>();
        buildTaskQueryRequest(queryParameter, hql, params);
        
        Number count = (Number) entityManager.findFirst(hql.toString(),
                                                        params.toArray());
        return count.intValue();
    }
    
    public int countMyTask(TaskQueryParameter queryParameter) {
        StringBuilder hql = new StringBuilder("select count(t.id) from Task t where t.parent is null and t.creator.id = ? ");
        List<Object> params = new ArrayList<Object>();
        params.add(queryParameter.getCreatorId());
        buildTaskQueryRequest(queryParameter, hql, params);
        
        Number count = (Number) entityManager.findFirst(hql.toString(),
                                                        params.toArray());
        return count.intValue();
    }
    
    public int countAllTask(TaskQueryParameter queryParameter) {
        StringBuilder hql = new StringBuilder("select count(t.id) from Task t where t.parent is null ");
        List<Object> params = new ArrayList<Object>();
        buildTaskQueryRequest(queryParameter, hql, params);
        
        Number count = (Number) entityManager.findFirst(hql.toString(),
                                                        params.toArray());
        return count.intValue();
    }
    
    public List<Map> getTaskList(TaskQueryParameter queryParameter,
                                 int start,
                                 int limit) {
        StringBuilder hql = new StringBuilder(QueryRepository.getInstance()
                                                             .get("hql.getTaskList")).append(" where t.parent is null ");
        List<Object> params = new ArrayList<Object>();
        buildTaskQueryRequest(queryParameter, hql, params);
        
        // 排序
        if (Task.IN_PROGRESS_STATUS.equals(queryParameter.getStatus()) || Task.ASSIGNED_STATUS.equals(queryParameter.getStatus())) {
            hql.append(" order by t.createDateTime desc");
        }
        else if (Task.COMPLETED_STATUS.equals(queryParameter.getStatus())) {
            hql.append(" order by t.locateDate desc");
        }
        else {
            hql.append(" order by t.createDateTime desc");
        }
        
        IPage<Map> page = entityManager.findPage(hql.toString(),
                                                 params.toArray(),
                                                 start,
                                                 limit);
        return page.getData();
    }
    
    public List<Map> getMyTaskList(TaskQueryParameter queryParameter,
                                   int start,
                                   int limit) {
        
        StringBuilder hql = new StringBuilder(QueryRepository.getInstance()
                                                             .get("hql.getTaskList")).append(" where t.parent is null and t.creator.id = ? ");
        // StringBuilder hql = new
        // StringBuilder("from Task t left join t.project where t.parent is null and t.creator.id = ? ");
        List<Object> params = new ArrayList<Object>();
        params.add(queryParameter.getCreatorId());
        buildTaskQueryRequest(queryParameter, hql, params);
        
        // 排序
        if (Task.ASSIGNED_STATUS.equals(queryParameter.getStatus()) || Task.IN_PROGRESS_STATUS.equals(queryParameter.getStatus())) {
            hql.append(" order by t.assignedDate desc");
        }
        else if (Task.COMPLETED_STATUS.equals(queryParameter.getStatus())) {
            hql.append(" order by t.endDate desc");
        }
        else {
            hql.append(" order by t.createDateTime desc");
        }
        
        IPage<Map> page = entityManager.findPage(hql.toString(),
                                                 params.toArray(),
                                                 start,
                                                 limit);
        return page.getData();
    }
    
    public List<Map> getAllTaskList(TaskQueryParameter queryParameter,
                                    int start,
                                    int limit) {
        StringBuilder hql = new StringBuilder(QueryRepository.getInstance()
                                                             .get("hql.getTaskList")).append(" where t.parent is null ");
        List<Object> params = new ArrayList<Object>();
        buildTaskQueryRequest(queryParameter, hql, params);
        
        // 排序
        if (Task.ASSIGNED_STATUS.equals(queryParameter.getStatus()) || Task.IN_PROGRESS_STATUS.equals(queryParameter.getStatus())) {
            hql.append(" order by t.assignedDate desc");
        }
        else if (Task.COMPLETED_STATUS.equals(queryParameter.getStatus())) {
            hql.append(" order by t.endDate desc");
        }
        else {
            hql.append(" order by t.createDateTime desc");
        }
        
        IPage<Map> page = entityManager.findPage(hql.toString(),
                                                 params.toArray(),
                                                 start,
                                                 limit);
        return page.getData();
    }
    
    /**
     * Task alias t User alias u
     * 
     * @param queryParameter
     * @param hql
     * @param params
     */
    private void buildTaskQueryRequest(TaskQueryParameter queryParameter,
                                       StringBuilder hql,
                                       List<Object> params) {
        if (queryParameter == null) {
            return;
        }
        if (StringUtils.isNotEmpty(queryParameter.getExecPersonId())) {
            hql.append(" and t.executePerson.id=? ");
            params.add(queryParameter.getExecPersonId());
        }
        if (StringUtils.isNotEmpty(queryParameter.getExecPersonIds())) {
            hql.append(" and t.executePerson.id in (");
            hql.append(queryParameter.getExecPersonIds()).append(") ");
        }
        if (StringUtils.isNotEmpty(queryParameter.getStatus())) {
            hql.append(" and t.status=? ");
            params.add(queryParameter.getStatus());
        }
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            if (queryParameter.getName().startsWith("处理人 ")) {
                hql.append(" and (t.executePerson.name like ?) ");
                params.add("%" + queryParameter.getName()
                                               .substring("处理人 ".length())
                           + "%");
            }
            else if (queryParameter.getName().startsWith("处理人　")) {
                hql.append(" and (t.executePerson.name like ?) ");
                params.add("%" + queryParameter.getName()
                                               .substring("处理人　".length())
                           + "%");
            }
            else if (queryParameter.getName().startsWith("发起人 ")) {
                hql.append(" and (t.creator.name like ?) ");
                params.add("%" + queryParameter.getName()
                                               .substring("发起人 ".length())
                           + "%");
            }
            else if (queryParameter.getName().startsWith("发起人　")) {
                hql.append(" and (t.creator.name like ?) ");
                params.add("%" + queryParameter.getName()
                                               .substring("发起人　".length())
                           + "%");
            }
            else if (queryParameter.getName().startsWith("项目 ")) {
                hql.append(" and (t.project.name like ?) ");
                params.add("%" + queryParameter.getName()
                                               .substring("项目 ".length()) + "%");
            }
            else if (queryParameter.getName().startsWith("项目　")) {
                hql.append(" and (t.project.name like ?) ");
                params.add("%" + queryParameter.getName()
                                               .substring("项目　".length()) + "%");
            }
            else {
                if (StringUtils.isNotEmpty(queryParameter.getProjectName())) {
                    hql.append(" and (t.name like ? or t.project.name like ?) ");
                    params.add("%" + queryParameter.getName() + "%");
                    params.add("%" + queryParameter.getProjectName() + "%");
                }else{ 
                	hql.append(" and (t.name like ?) ");
                    params.add("%" + queryParameter.getName() + "%");
                }                
            }
        }
        if (StringUtils.isNotEmpty(queryParameter.getProjectId())) {
            hql.append(" and t.project.id=? ");
            params.add(queryParameter.getProjectId());
        }
        if (StringUtils.isNotEmpty(queryParameter.getExecOrganId())) {
            Organization org = entityManager.find(Organization.class,
                                                  queryParameter.getExecOrganId());
            if (org != null) {
                hql.append(" and t.executeOrganization.innerCode like ?");
                params.add(org.getInnerCode() + "%");
            }
        }
        
        // 计划时间
        if (queryParameter.getPlanStartDate() != null) {
            hql.append(" and t.planStartDate>=? ");
            params.add(queryParameter.getPlanStartDate());
        }
        if (queryParameter.getPlanEndDate() != null) {
            hql.append(" and t.planEndDate<? ");
            params.add(DateUtils.addDay(queryParameter.getPlanEndDate(), 1));
        }
        
        // 实际时间
        if (queryParameter.getStartDate() != null) {
            hql.append(" and t.startDate>=? ");
            params.add(queryParameter.getStartDate());
        }
        if (queryParameter.getEndDate() != null) {
            hql.append(" and t.endDate<? ");
            params.add(DateUtils.addDay(queryParameter.getEndDate(), 1));
        }
        
        // 创建时间
        if (queryParameter.getCreateStartDate() != null) {
            hql.append(" and t.createDateTime>=? ");
            params.add(queryParameter.getCreateStartDate());
        }
        if (queryParameter.getCreateEndDate() != null) {
            hql.append(" and t.createDateTime<? ");
            params.add(DateUtils.addDay(queryParameter.getCreateEndDate(), 1));
        }
        
        // 定位时间
        if (queryParameter.getLocateStartDate() != null) {
            hql.append(" and t.locateDate>=? ");
            params.add(queryParameter.getLocateStartDate());
        }
        if (queryParameter.getLocateEndDate() != null) {
            hql.append(" and t.locateDate<? ");
            params.add(DateUtils.addDay(queryParameter.getLocateEndDate(), 1));
        }
        
        if (queryParameter.getDataScope() == DataScope.Inside) {
            hql.append(" and t.sourceSystem is null and t.sourceId is null ");
        }
        else if (queryParameter.getDataScope() == DataScope.Outside) {
            hql.append(" and t.sourceSystem is not null and t.sourceId is not null ");
        }
        
        if (StringUtils.isNotBlank(queryParameter.getCreateType())) {
            hql.append(" and t.createType=? ");
            params.add(queryParameter.getCreateType());
        }      
    }

    @Transactional
    public Task getTaskMap(String id) {
    	Task task = (Task) entityManager.findFirst("from Task t where t.id=?", new Object[] { id });
		if (null != task) {
			if (!Hibernate.isInitialized(task.getTemplate())) {
				Hibernate.initialize(task.getTemplate());
			}
			if (!Hibernate.isInitialized(task.getProject())) {
				Hibernate.initialize(task.getProject());
			}
			if (!Hibernate.isInitialized(task.getExecuteOrganization())) {
				Hibernate.initialize(task.getExecuteOrganization());
			}
			if (!Hibernate.isInitialized(task.getExecutePerson())) {
				Hibernate.initialize(task.getExecutePerson());
			}
			if (!Hibernate.isInitialized(task.getCreator())) {
				Hibernate.initialize(task.getCreator());
			}
			if (!Hibernate.isInitialized(task.getStationType())) {
				Hibernate.initialize(task.getStationType());
			}
			if (!Hibernate.isInitialized(task.getSiteProperties())) {
				Hibernate.initialize(task.getSiteProperties());
			}
			if (!Hibernate.isInitialized(task.getPropertyOwnership())) {
				Hibernate.initialize(task.getPropertyOwnership());
			}
			if (!Hibernate.isInitialized(task.getAreaType())) {
				Hibernate.initialize(task.getAreaType());
			}
			if (!Hibernate.isInitialized(task.getConstructionModel())) {
				Hibernate.initialize(task.getConstructionModel());
			}
			if (!Hibernate.isInitialized(task.getSiteValid())) {
				Hibernate.initialize(task.getSiteValid());
			}
    	}

        if(task.getProspectRecod() == null){
        	ProspectRecod prospectRecod = new ProspectRecod(task);
        	entityManager.persist(prospectRecod);
        	task.setProspectRecod(prospectRecod);
        	entityManager.persist(task);
        }
        
    	return task;
        /*Task task = new Task();
        StringBuilder hql = new StringBuilder(QueryRepository.getInstance()
                                                             .get("hql.getTask"));
        Map map = (Map) entityManager.findFirst(hql.toString(),
                                                new Object[] { id });
        if (map != null) {
            BeanUtils.copyProperties(task, map);
            // String taskDistrict = (String) map.get("taskDistrict");
            // if (StringUtils.isNotEmpty(taskDistrict)) {
            // District district = entityManager.find(District.class,
            // taskDistrict);
            // task.setDistrict(district);
            // }
            String taskCreator = (String) map.get("taskCreator");
            if (StringUtils.isNotEmpty(taskCreator)) {
                User creator = entityManager.find(User.class, taskCreator);
                task.setCreator(creator);
            }
            String projectId = (String) map.get("taskProject");
            if (StringUtils.isNotEmpty(projectId)) {
                Project project = entityManager.find(Project.class, projectId);
                task.setProject(project);
            }
            String executeOrganizationId = (String) map.get("taskExecuteOrganization");
            if (StringUtils.isNotEmpty(executeOrganizationId)) {
                Organization executeOrganization = entityManager.find(Organization.class,
                                                                      executeOrganizationId);
                task.setExecuteOrganization(executeOrganization);
            }
            String executePersonId = (String) map.get("taskExecutePerson");
            if (StringUtils.isNotEmpty(executePersonId)) {
                User executePerson = entityManager.find(User.class,
                                                        executePersonId);
                task.setExecutePerson(executePerson);
            }
            String templateId = (String) map.get("templateId");
            if (StringUtils.isNotEmpty(templateId)) {
            	IndicatorTemplate template = entityManager.find(IndicatorTemplate.class, templateId);
            	task.setTemplate(template);
            }
        }
        return task;*/
    }
    
    public Task getTask(String id) {
        return (Task) entityManager.findFirst("from Task t left join fetch t.project left join fetch t.executeOrganization left join fetch t.executePerson left join fetch t.creator left join fetch t.parent where t.id = ? ",
                                              new Object[] { id });
    }
    
    public List<Map> getStatsUserList(TaskQueryParameter queryParameter) {
        StringBuilder sql = new StringBuilder(QueryRepository.getInstance()
                                                             .get("sql.getStatsUserList"));
        List<Object> params = new ArrayList<Object>();
        buildStatsQueryRequest(queryParameter, sql, params);
        
        StringBuilder countSql = new StringBuilder(QueryRepository.getInstance()
                                                                  .get("sql.getStatsUserCount"));
        buildStatsQueryRequest(queryParameter,
                               countSql,
                               new ArrayList<Object>());
        
        // 排序
        sql.append(" order by o.name,u.name");
        
        IPage<Map> page = entityManager.nativeFindPage(countSql.toString(),
                                                       sql.toString(),
                                                       params.toArray(),
                                                       queryParameter.getStart(),
                                                       queryParameter.getLimit());
        return page.getData();
    }
    
    public int countStatsUser(TaskQueryParameter queryParameter) {
        StringBuilder sql = new StringBuilder(QueryRepository.getInstance()
                                                             .get("sql.getStatsUserCount"));
        List<Object> params = new ArrayList<Object>();
        buildStatsQueryRequest(queryParameter, sql, params);
        Map map = (Map) entityManager.nativeFindFirst(sql.toString(),
                                                      params.toArray());
        return ((Number) map.get("userCount")).intValue();
    }
    
    private void buildStatsQueryRequest(TaskQueryParameter queryParameter,
                                        StringBuilder sql,
                                        List<Object> params) {
        if (queryParameter == null) {
            return;
        }
        if (StringUtils.isNotEmpty(queryParameter.getExecPersonId())) {
            sql.append(" and t.exec_person_id=? ");
            params.add(queryParameter.getExecPersonId());
        }
        if (StringUtils.isNotEmpty(queryParameter.getStatus())) {
            sql.append(" and t.status=? ");
            params.add(queryParameter.getStatus());
        }
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            sql.append(" and (t.name like ?) ");
            params.add("%" + queryParameter.getName() + "%");
        }
        if (StringUtils.isNotEmpty(queryParameter.getProjectId())) {
            sql.append(" and t.proj_id=? ");
            params.add(queryParameter.getProjectId());
        }
        if (StringUtils.isNotEmpty(queryParameter.getExecOrganId())) {
            Organization org = entityManager.find(Organization.class,
                                                  queryParameter.getExecOrganId());
            if (org != null) {
                sql.append(" and o.inner_code like ?");
                params.add(org.getInnerCode() + "%");
            }
        }
        
        // 计划时间
        if (queryParameter.getPlanStartDate() != null) {
            sql.append(" and t.plan_end_date>=? ");
            params.add(queryParameter.getPlanStartDate());
        }
        if (queryParameter.getPlanEndDate() != null) {
            sql.append(" and t.plan_end_date<? ");
            params.add(DateUtils.addDay(queryParameter.getPlanEndDate(), 1));
        }
        
        // 实际时间
        if (queryParameter.getStartDate() != null) {
            sql.append(" and t.real_end_date>=? ");
            params.add(queryParameter.getStartDate());
        }
        if (queryParameter.getEndDate() != null) {
            sql.append(" and t.real_end_date<? ");
            params.add(DateUtils.addDay(queryParameter.getEndDate(), 1));
        }
        
        // 创建时间
        if (queryParameter.getCreateStartDate() != null) {
            sql.append(" and t.create_datetime>=? ");
            params.add(queryParameter.getCreateStartDate());
        }
        if (queryParameter.getCreateEndDate() != null) {
            sql.append(" and t.create_datetime<? ");
            params.add(DateUtils.addDay(queryParameter.getCreateEndDate(), 1));
        }
        
        // 定位时间
        if (queryParameter.getLocateStartDate() != null) {
            sql.append(" and t.locate_date>=? ");
            params.add(queryParameter.getLocateStartDate());
        }
        if (queryParameter.getLocateEndDate() != null) {
            sql.append(" and t.locate_date<? ");
            params.add(DateUtils.addDay(queryParameter.getLocateEndDate(), 1));
        }
        
        if (queryParameter.getDataScope() == DataScope.Inside) {
            sql.append(" and t.source_system is null and t.source_id is null ");
        }
        else if (queryParameter.getDataScope() == DataScope.Outside) {
            sql.append(" and t.source_system is not null and t.source_id is not null ");
        }
        
        if (StringUtils.isNotBlank(queryParameter.getCreateType())) {
            sql.append(" and t.create_type=? ");
            params.add(queryParameter.getCreateType());
        }
    }
    
    public List<Task> getNoAddressTaskList(String id) {
        StringBuilder hql = new StringBuilder(QueryRepository.getInstance()
                                                             .get("hql.getNoAddressTaskList"));
        
        List<Object> params = new ArrayList<Object>();
        hql.append(" and t.status=? ");
        params.add(Task.COMPLETED_STATUS);
        
        if (StringUtils.isNotBlank(id)) {
            hql.append(" and t.id=? ");
            params.add(id);
        }
        
        return entityManager.findList(hql.toString(), params.toArray());
    }
    
    @Override
    public List<Map> listBySaleDemo(TaskQueryParameter queryParameter) {
        StringBuilder hql = new StringBuilder(QueryRepository.getInstance()
                                                             .get("hql.getTaskList")).append(" where t.parent is null ");
        List<Object> params = new ArrayList<Object>();
        buildTaskSaleDemoRequest(queryParameter, hql, params);
        
        // 排序
        if (Task.IN_PROGRESS_STATUS.equals(queryParameter.getStatus()) || Task.ASSIGNED_STATUS.equals(queryParameter.getStatus())) {
            hql.append(" order by t.createDateTime desc");
        }
        else if (Task.COMPLETED_STATUS.equals(queryParameter.getStatus())) {
            hql.append(" order by t.locateDate desc");
        }
        else {
            hql.append(" order by t.createDateTime desc");
        }
        
        IPage<Map> page = entityManager.findPage(hql.toString(),
                                                 params.toArray(),
                                                 queryParameter.getStart(),
                                                 queryParameter.getLimit());
        return page.getData();
    }
    
    @Override
    public int countBySaleDemo(TaskQueryParameter queryParameter) {
        StringBuilder hql = new StringBuilder("select count(t.id) from Task t where t.parent is null ");
        List<Object> params = new ArrayList<Object>();
        buildTaskSaleDemoRequest(queryParameter, hql, params);
        
        Number count = (Number) entityManager.findFirst(hql.toString(),
                                                        params.toArray());
        return count.intValue();
    }
    
    /**
     * Task alias t User alias u
     * 
     * @param queryParameter
     * @param hql
     * @param params
     */
    private void buildTaskSaleDemoRequest(TaskQueryParameter queryParameter,
                                          StringBuilder hql,
                                          List<Object> params) {
        if (queryParameter == null) {
            return;
        }
        if (StringUtils.isNotEmpty(queryParameter.getExecPersonId())) {
            hql.append(" and t.executePerson.id=? ");
            params.add(queryParameter.getExecPersonId());
        }
        if (StringUtils.isNotEmpty(queryParameter.getExecPersonIds())) {
            hql.append(" and t.executePerson.id in (");
            hql.append(queryParameter.getExecPersonIds()).append(") ");
        }
        if (StringUtils.isNotEmpty(queryParameter.getStatus())) {
            hql.append(" and t.status=? ");
            params.add(queryParameter.getStatus());
        }
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            if (queryParameter.getName().startsWith("处理人 ")) {
                hql.append(" and (t.executePerson.name like ?) ");
                params.add("%" + queryParameter.getName()
                                               .substring("处理人 ".length())
                           + "%");
            }
            else if (queryParameter.getName().startsWith("处理人　")) {
                hql.append(" and (t.executePerson.name like ?) ");
                params.add("%" + queryParameter.getName()
                                               .substring("处理人　".length())
                           + "%");
            }
            else if (queryParameter.getName().startsWith("发起人 ")) {
                hql.append(" and (t.creator.name like ?) ");
                params.add("%" + queryParameter.getName()
                                               .substring("发起人 ".length())
                           + "%");
            }
            else if (queryParameter.getName().startsWith("发起人　")) {
                hql.append(" and (t.creator.name like ?) ");
                params.add("%" + queryParameter.getName()
                                               .substring("发起人　".length())
                           + "%");
            }
            else if (queryParameter.getName().startsWith("项目 ")) {
                hql.append(" and (t.project.name like ?) ");
                params.add("%" + queryParameter.getName()
                                               .substring("项目 ".length()) + "%");
            }
            else if (queryParameter.getName().startsWith("项目　")) {
                hql.append(" and (t.project.name like ?) ");
                params.add("%" + queryParameter.getName()
                                               .substring("项目　".length()) + "%");
            }
            else {
                hql.append(" and (t.name like ?) ");
                params.add("%" + queryParameter.getName() + "%");
            }
        }
        if (StringUtils.isNotEmpty(queryParameter.getProjectId())) {
            hql.append(" and t.project.id=? ");
            params.add(queryParameter.getProjectId());
        }
        if (StringUtils.isNotEmpty(queryParameter.getExecOrganId())) {
            Organization org = entityManager.find(Organization.class,
                                                  queryParameter.getExecOrganId());
            if (org != null) {
                hql.append(" and t.executeOrganization.innerCode like ?");
                params.add(org.getInnerCode() + "%");
            }
        }
        
        // 创建时间
        if (queryParameter.getCreateStartDate() != null) {
            hql.append(" and t.createDateTime>=? ");
            params.add(queryParameter.getCreateStartDate());
        }
        if (queryParameter.getCreateEndDate() != null) {
            hql.append(" and t.createDateTime<? ");
            params.add(DateUtils.addDay(queryParameter.getCreateEndDate(), 1));
        }
        
        // 合并计划完成时间和实际定位时间
        if (queryParameter.getLocateStartDate() != null && queryParameter.getLocateEndDate() != null) {
            Date queryEndDate = DateUtils.addDay(queryParameter.getLocateEndDate(),
                                                 1);
            hql.append(" and ((t.locateDate>=? and t.locateDate<?) or (t.planEndDate>=? and t.planEndDate<?))");
            params.add(queryParameter.getLocateStartDate());
            params.add(queryEndDate);
            params.add(queryParameter.getLocateStartDate());
            params.add(queryEndDate);
        }
        
        if (queryParameter.getDataScope() == DataScope.Inside) {
            hql.append(" and t.sourceSystem is null and t.sourceId is null ");
        }
        else if (queryParameter.getDataScope() == DataScope.Outside) {
            hql.append(" and t.sourceSystem is not null and t.sourceId is not null ");
        }
        
        if (StringUtils.isNotBlank(queryParameter.getCreateType())) {
            hql.append(" and t.createType=? ");
            params.add(queryParameter.getCreateType());
        }
        
    }
    
    @Override
    public int countStats(TaskStatsByProjectQueryParameter queryParameter) {
        List<Object> params = new ArrayList<Object>();
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) as recordCount from (");
        sql.append(QueryRepository.getInstance().get("sql.listStatsByProject"));
        buildStatsQueryRequest(queryParameter, sql, params);
        sql.append(" group by p.id) aa ");
        Map map = (Map) entityManager.nativeFindFirst(sql.toString(),
                                                      params.toArray());
        return ((Number) map.get("recordCount")).intValue();
    }
    
    @Override
    public List<Map> listStats(TaskStatsByProjectQueryParameter queryParameter) {
        StringBuilder sql = new StringBuilder(QueryRepository.getInstance()
                                                             .get("sql.listStatsByProject"));
        List<Object> params = new ArrayList<Object>();
        buildStatsQueryRequest(queryParameter, sql, params);
        sql.append(" group by p.id order by p.year desc, p.name ");
        IPage<Map> page = (IPage<Map>) entityManager.nativeFindPage(sql.toString(),
                                                                    params.toArray(),
                                                                    queryParameter.getStart(),
                                                                    queryParameter.getLimit());
        return page.getData();
    }
    
    private void buildStatsQueryRequest(TaskStatsByProjectQueryParameter queryParameter,
                                        StringBuilder sql,
                                        List<Object> params) {
        if (queryParameter == null) {
            return;
        }
        
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            sql.append(" and p.name like ? ");
            String like = "%" + queryParameter.getName() + "%";
            params.add(like);
        }
        if (StringUtils.isNotBlank(queryParameter.getOrganId())) {
            sql.append(" and p.organ_id=? ");
            params.add(queryParameter.getOrganId());
        }
        if (StringUtils.isNotBlank(queryParameter.getAreaId())) {
            sql.append(" and p.area_id=? ");
            params.add(queryParameter.getAreaId());
        }
        if (StringUtils.isNotBlank(queryParameter.getSpecialId())) {
            sql.append(" and p.special_id=? ");
            params.add(queryParameter.getSpecialId());
        }
        if (StringUtils.isNotBlank(queryParameter.getCustomerId())) {
            sql.append(" and p.customer_id=? ");
            params.add(queryParameter.getCustomerId());
        }
        
        if (queryParameter.getClose() != null) {
            sql.append(" and p.close=? ");
            params.add(queryParameter.getClose());
        }
        if (queryParameter.getYear() != null) {
            sql.append(" and p.year=? ");
            params.add(queryParameter.getYear());
        }
        
        // 定位时间
        if (queryParameter.getLocateStartDate() != null) {
            sql.append(" and t.locate_date>=? ");
            params.add(queryParameter.getLocateStartDate());
        }
        if (queryParameter.getLocateEndDate() != null) {
            sql.append(" and t.locate_date<? ");
            params.add(DateUtils.addDay(queryParameter.getLocateEndDate(), 1));
        }
        
        if (queryParameter.getDataScope() == DataScope.Inside) {
            sql.append(" and p.sourceSystem is null and p.sourceId is null ");
        }
        else if (queryParameter.getDataScope() == DataScope.Outside) {
            sql.append(" and p.sourceSystem is not null and p.sourceId is not null ");
        }
    }
    
    @Transactional
    public ProspectRecod getProspectMapByTaskId(String taskId) {
    	ProspectRecod prospectRecod = (ProspectRecod) entityManager.findFirst("from ProspectRecod t where t.task.id=?", new Object[] { taskId });
    	return getProspectRecodMap(prospectRecod);
    }
    
    @Transactional
    public ProspectRecod getProspectMapById(String id) {
    	ProspectRecod prospectRecod = (ProspectRecod) entityManager.findFirst("from ProspectRecod t where t.id=?", new Object[] { id });
    	return getProspectRecodMap(prospectRecod);
    }

	private ProspectRecod getProspectRecodMap(ProspectRecod prospectRecod) {
		if (null != prospectRecod) {
			if (!Hibernate.isInitialized(prospectRecod.getTask())) {
				Hibernate.initialize(prospectRecod.getTask());
				if (!Hibernate.isInitialized(prospectRecod.getTask().getTemplate())) {
					Hibernate.initialize(prospectRecod.getTask().getTemplate());
				}
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice01())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice01());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice02())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice02());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice03())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice03());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice04())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice04());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice05())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice05());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice06())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice06());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice07())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice07());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice08())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice08());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice09())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice09());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice13())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice13());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice17())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice17());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice18())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice18());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice19())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice19());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice20())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice20());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice21())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice21());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice22())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice22());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice26())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice26());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice31())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice31());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCprimaryDevice32())) {
				Hibernate.initialize(prospectRecod.getCprimaryDevice32());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtefddprimaryDevice01())) {
				Hibernate.initialize(prospectRecod.getLtefddprimaryDevice01());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtefddprimaryDevice02())) {
				Hibernate.initialize(prospectRecod.getLtefddprimaryDevice02());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtefddprimaryDevice04())) {
				Hibernate.initialize(prospectRecod.getLtefddprimaryDevice04());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtefddprimaryDevice05())) {
				Hibernate.initialize(prospectRecod.getLtefddprimaryDevice05());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtefddprimaryDevice06())) {
				Hibernate.initialize(prospectRecod.getLtefddprimaryDevice06());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtefddprimaryDevice13())) {
				Hibernate.initialize(prospectRecod.getLtefddprimaryDevice13());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtefddprimaryDevice14())) {
				Hibernate.initialize(prospectRecod.getLtefddprimaryDevice14());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtefddprimaryDevice15())) {
				Hibernate.initialize(prospectRecod.getLtefddprimaryDevice15());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtefddprimaryDevice16())) {
				Hibernate.initialize(prospectRecod.getLtefddprimaryDevice16());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtefddprimaryDevice17())) {
				Hibernate.initialize(prospectRecod.getLtefddprimaryDevice17());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtefddprimaryDevice23())) {
				Hibernate.initialize(prospectRecod.getLtefddprimaryDevice23());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtefddprimaryDevice25())) {
				Hibernate.initialize(prospectRecod.getLtefddprimaryDevice25());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtefddprimaryDevice26())) {
				Hibernate.initialize(prospectRecod.getLtefddprimaryDevice26());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtetddprimaryDevice01())) {
				Hibernate.initialize(prospectRecod.getLtetddprimaryDevice01());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtetddprimaryDevice02())) {
				Hibernate.initialize(prospectRecod.getLtetddprimaryDevice02());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtetddprimaryDevice04())) {
				Hibernate.initialize(prospectRecod.getLtetddprimaryDevice04());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtetddprimaryDevice05())) {
				Hibernate.initialize(prospectRecod.getLtetddprimaryDevice05());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtetddprimaryDevice06())) {
				Hibernate.initialize(prospectRecod.getLtetddprimaryDevice06());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtetddprimaryDevice13())) {
				Hibernate.initialize(prospectRecod.getLtetddprimaryDevice13());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtetddprimaryDevice15())) {
				Hibernate.initialize(prospectRecod.getLtetddprimaryDevice15());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtetddprimaryDevice16())) {
				Hibernate.initialize(prospectRecod.getLtetddprimaryDevice16());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtetddprimaryDevice17())) {
				Hibernate.initialize(prospectRecod.getLtetddprimaryDevice17());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtetddprimaryDevice22())) {
				Hibernate.initialize(prospectRecod.getLtetddprimaryDevice22());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtetddprimaryDevice25())) {
				Hibernate.initialize(prospectRecod.getLtetddprimaryDevice25());
			}
			if (!Hibernate.isInitialized(prospectRecod
					.getLtetddprimaryDevice26())) {
				Hibernate.initialize(prospectRecod.getLtetddprimaryDevice26());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCantennaFeed01())) {
				Hibernate.initialize(prospectRecod.getCantennaFeed01());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCantennaFeed02())) {
				Hibernate.initialize(prospectRecod.getCantennaFeed02());
			}
			if (!Hibernate.isInitialized(prospectRecod.getCantennaFeed14())) {
				Hibernate.initialize(prospectRecod.getCantennaFeed14());
			}
			if (!Hibernate
					.isInitialized(prospectRecod.getLtefddantennaFeed01())) {
				Hibernate.initialize(prospectRecod.getLtefddantennaFeed01());
			}
			if (!Hibernate
					.isInitialized(prospectRecod.getLtefddantennaFeed02())) {
				Hibernate.initialize(prospectRecod.getLtefddantennaFeed02());
			}
			if (!Hibernate
					.isInitialized(prospectRecod.getLtefddantennaFeed03())) {
				Hibernate.initialize(prospectRecod.getLtefddantennaFeed03());
			}
			if (!Hibernate
					.isInitialized(prospectRecod.getLtefddantennaFeed14())) {
				Hibernate.initialize(prospectRecod.getLtefddantennaFeed14());
			}
			if (!Hibernate
					.isInitialized(prospectRecod.getLtetddantennaFeed01())) {
				Hibernate.initialize(prospectRecod.getLtetddantennaFeed01());
			}
			if (!Hibernate
					.isInitialized(prospectRecod.getLtetddantennaFeed02())) {
				Hibernate.initialize(prospectRecod.getLtetddantennaFeed02());
			}
			if (!Hibernate
					.isInitialized(prospectRecod.getLtetddantennaFeed03())) {
				Hibernate.initialize(prospectRecod.getLtetddantennaFeed03());
			}
			if (!Hibernate
					.isInitialized(prospectRecod.getLtetddantennaFeed14())) {
				Hibernate.initialize(prospectRecod.getLtetddantennaFeed14());
			}
			if (!Hibernate.isInitialized(prospectRecod.getPowerSupply01())) {
				Hibernate.initialize(prospectRecod.getPowerSupply01());
			}
			if (!Hibernate.isInitialized(prospectRecod.getPowerSupply02())) {
				Hibernate.initialize(prospectRecod.getPowerSupply02());
			}
			if (!Hibernate.isInitialized(prospectRecod.getPowerSupply03())) {
				Hibernate.initialize(prospectRecod.getPowerSupply03());
			}
			if (!Hibernate.isInitialized(prospectRecod.getPowerSupply06())) {
				Hibernate.initialize(prospectRecod.getPowerSupply06());
			}
			if (!Hibernate.isInitialized(prospectRecod.getPowerSupply07())) {
				Hibernate.initialize(prospectRecod.getPowerSupply07());
			}
			if (!Hibernate.isInitialized(prospectRecod.getPowerSupply08())) {
				Hibernate.initialize(prospectRecod.getPowerSupply08());
			}
			if (!Hibernate.isInitialized(prospectRecod.getPowerSupply13())) {
				Hibernate.initialize(prospectRecod.getPowerSupply13());
			}
			if (!Hibernate.isInitialized(prospectRecod.getPowerSupply16())) {
				Hibernate.initialize(prospectRecod.getPowerSupply16());
			}
			if (!Hibernate.isInitialized(prospectRecod.getPowerSupply24())) {
				Hibernate.initialize(prospectRecod.getPowerSupply24());
			}
			if (!Hibernate.isInitialized(prospectRecod.getPowerSupply27())) {
				Hibernate.initialize(prospectRecod.getPowerSupply27());
			}
			if (!Hibernate.isInitialized(prospectRecod.getPowerSupply28())) {
				Hibernate.initialize(prospectRecod.getPowerSupply28());
			}
			if (!Hibernate.isInitialized(prospectRecod.getPowerSupply31())) {
				Hibernate.initialize(prospectRecod.getPowerSupply31());
			}
			if (!Hibernate.isInitialized(prospectRecod.getMachineRoom01())) {
				Hibernate.initialize(prospectRecod.getMachineRoom01());
			}
			if (!Hibernate.isInitialized(prospectRecod.getMachineRoom02())) {
				Hibernate.initialize(prospectRecod.getMachineRoom02());
			}
			if (!Hibernate.isInitialized(prospectRecod.getMachineRoom03())) {
				Hibernate.initialize(prospectRecod.getMachineRoom03());
			}
			if (!Hibernate.isInitialized(prospectRecod.getMachineRoom06())) {
				Hibernate.initialize(prospectRecod.getMachineRoom06());
			}
			if (!Hibernate.isInitialized(prospectRecod.getMachineRoom07())) {
				Hibernate.initialize(prospectRecod.getMachineRoom07());
			}
			if (!Hibernate.isInitialized(prospectRecod.getMachineRoom11())) {
				Hibernate.initialize(prospectRecod.getMachineRoom11());
			}
			if (!Hibernate.isInitialized(prospectRecod.getMachineRoom12())) {
				Hibernate.initialize(prospectRecod.getMachineRoom12());
			}
			if (!Hibernate.isInitialized(prospectRecod.getMachineRoom13())) {
				Hibernate.initialize(prospectRecod.getMachineRoom13());
			}
			if (!Hibernate.isInitialized(prospectRecod.getMachineRoom14())) {
				Hibernate.initialize(prospectRecod.getMachineRoom14());
			}
			if (!Hibernate.isInitialized(prospectRecod.getMachineRoom15())) {
				Hibernate.initialize(prospectRecod.getMachineRoom15());
			}
			if (!Hibernate.isInitialized(prospectRecod.getMachineRoom16())) {
				Hibernate.initialize(prospectRecod.getMachineRoom16());
			}
			if (!Hibernate.isInitialized(prospectRecod.getStructuralMast01())) {
				Hibernate.initialize(prospectRecod.getStructuralMast01());
			}
			if (!Hibernate.isInitialized(prospectRecod.getStructuralMast02())) {
				Hibernate.initialize(prospectRecod.getStructuralMast02());
			}
			if (!Hibernate.isInitialized(prospectRecod.getStructuralMast03())) {
				Hibernate.initialize(prospectRecod.getStructuralMast03());
			}
			if (!Hibernate.isInitialized(prospectRecod.getStructuralMast12())) {
				Hibernate.initialize(prospectRecod.getStructuralMast12());
			}
		}
		return prospectRecod;
	}
}
